import os

import pandas as pd
from sqlalchemy import create_engine
import mysql.connector

import util.mydecorator as mydecorator
import util.lib as lib

from daily import get_daily_df

# 使用示例
mysql_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'xiaohashu'
}
def build():

    """
    基础pandas数据写入MySQL
    """
    # 创建数据库连接字符串
    mysql_url = f"mysql+mysqlconnector://{mysql_config['user']}:{mysql_config['password']}@{mysql_config['host']}/{mysql_config['database']}"

    # 创建与 SQLite 数据库的连接
    engine = create_engine(mysql_url)

    #build_daily_data(engine)
    #build_month_data(engine)
    #build_week_data(engine)
    build_gb_data(engine)
    #build_north_bound_data(engine)
    #build_daily_gb_data(engine)

    print("数据导入成功！")

def build_daily_data(engine):
    shares = lib.lib_get_all_stock()

    df = None
    for v in shares.iterrows():
        stock = v[1]['name']
        category = v[1]['category']
        wg_industry = v[1]['wg_industry']

        tmp = get_daily_df(stock)[-2200:]
        print(stock)
        tmp['stock'] = stock
        tmp['category'] = category
        tmp['wg_industry'] = wg_industry
        tmp['date'] = tmp.index.strftime('%Y-%m-%d')
        if tmp is None:
            df = tmp
        else:
            df = pd.concat([df, tmp])

    df[["date","stock",
        "close","open","low","high",
        "level_point","rel_lp","drawdown",
        "volume","money",
        "pct","pct_3d","pct_5d",
        "gt5d","gt10d","gt15d","gt20d","gt30d","gt60d","gt300d",
        "lowGt20d","lowGt30d",
        "category","wg_industry",
        ]
       ].to_sql('daily', con=engine, if_exists='replace', index=False)  # if_exists: 'fail', 'replace', 'append'


def build_month_data(engine):
    print("month_data")
    data = lib.lib_get_month_data()
    df = None
    for k,v in data.items():
        v['date'] = v.index
        if df is None:
            df = v
        else:
            df = pd.concat([df, v])

    df[["date","stock","close","drawdown","month_pct","next_month_pct","pct_sum","inc_num","level_point","rel_lp","gt1200d","category"]
       ].to_sql('month', con=engine, if_exists='replace', index=False)  # if_exists: 'fail', 'replace', 'append'

def build_week_data(engine):
    print("week_data")
    data = lib.lib_get_week_data()
    df = None
    for k,v in data.items():
        v['date'] = v.index
        if df is None:
            df = v
        else:
            df = pd.concat([df, v])

    df.to_sql('week', con=engine, if_exists='replace', index=False)  # if_exists: 'fail', 'replace', 'append'

def build_gb_data(engine):
    print("gb_data")
    data = lib.lib_get_gb_data()
    df = None
    for k,v in data.items():
        v['date'] = v.index
        if df is None:
            df = v
        else:
            df = pd.concat([df, v])

    df.to_sql('gb', con=engine, if_exists='replace', index=False)  # if_exists: 'fail', 'replace', 'append'

def build_daily_gb_data(engine):
    print("daily_gb_data")
    data = lib.lib_get_daily_gb_data()
    df = None
    for k,v in data.items():
        v['date'] = v.index.strftime("%Y-%m-%d")
        if df is None:
            df = v
        else:
            df = pd.concat([df, v])
    df.to_sql('daily_gb', engine, if_exists='replace', index=False)

def build_north_bound_data(engine):
    print("north_bound_data")
    data = lib.lib_get_north_bound_data()

    df = None
    for k,v in data.items():
        if df is None:
            df = v
        else:
            df = pd.concat([df, v])

    df[["trade_date","name","vol","ratio"]].to_sql('north_bound', con=engine, if_exists='replace', index=False)  # if_exists: 'fail', 'replace', 'append'

def setIndex():
    global db

    # 连接到数据库
    conn = sqlite3.connect(db)
    cursor = conn.cursor()

    # week
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_week_stock ON week (stock)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_week_date ON week (date)')

    # month
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_month_stock ON month (stock)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_month_date ON month (date)')

    # daily
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_daily_stock ON daily (stock)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_daily_date ON daily (date)')

    # gb
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_gb_stock ON gb (stock)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_gb_date ON gb (date)')


    # 提交更改
    conn.commit()


def select():
    global db

    conn = sqlite3.connect(db)
    df = pd.read_sql_query("SELECT * FROM daily where stock='白云机场'", conn)

    print(df.head())

@mydecorator.calTime
def job():
    build()

if __name__ == '__main__':
    job()
    #setIndex()
    #select()